﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Collections.Generic;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using Jungle.Code.DAL;

namespace Jungle.Prodamo.PriceManager
{

    public class Category
    {
        // Private Fields
        private int categoryId;
        private string name;
        private string alias;
        private string imageUrl;
        private int parentId;
        private bool isVisible;

        public List<Category> CategoryList;

        // Properties
        #region Properties
        public int CategoryId
        {
            get{return categoryId;}
            set{categoryId = value;}
        }
        public string Name
        {
            get { return name; }
            set { name = value; }
        }
        public string Alias
        {
            get { return alias; }
            set { alias = value; }
        }
        public string ImageUrl
        {
            get { return imageUrl; }
            set { imageUrl = value; }
        }
        public int ParentId
        {
            get { return parentId; }
            set { parentId = value; }
        }
        public bool IsVisible
        {
            get { return isVisible; }
            set { isVisible = value; }
        }
        #endregion

        public void Add()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Categories_Add", connect);
                SqlParameter param_Name = command.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
                SqlParameter param_Alias = command.Parameters.Add("@Alias", SqlDbType.NVarChar, 255);
                SqlParameter param_ImageUrl = command.Parameters.Add("@ImageUrl", SqlDbType.NVarChar, 255);
                SqlParameter param_ParentId = command.Parameters.Add("@ParentId", SqlDbType.Int);
                SqlParameter param_IsVisible = command.Parameters.Add("@IsVisible", SqlDbType.Bit);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                param_Name.Value = name;
                param_Alias.Value = alias;
                param_ImageUrl.Value = imageUrl;
                param_ParentId.Value = parentId;
                param_IsVisible.Value = isVisible;

                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    categoryId = Convert.ToInt32(reader.GetValue(0));
                }
                command.Connection.Close();

            }
        }
        public void Update()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Categories_Update", connect);
                SqlParameter param_CategoryId = command.Parameters.Add("@CategoryId", SqlDbType.Int);
                SqlParameter param_Name = command.Parameters.Add("@Name", SqlDbType.NVarChar, 100);
                SqlParameter param_Alias = command.Parameters.Add("@Alias", SqlDbType.NVarChar, 255);
                SqlParameter param_ImageUrl = command.Parameters.Add("@ImageUrl", SqlDbType.NVarChar, 255);
                SqlParameter param_ParentId = command.Parameters.Add("@ParentId", SqlDbType.Int);
                SqlParameter param_IsVisible = command.Parameters.Add("@IsVisible", SqlDbType.Bit);

                command.CommandType = CommandType.StoredProcedure;
                param_CategoryId.Value = categoryId;
                param_Name.Value = name;
                param_Alias.Value = alias;
                param_ImageUrl.Value = imageUrl;
                param_ParentId.Value = parentId;
                param_IsVisible.Value = isVisible;

                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();

            }
        }
        public void Delete()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Categories_Delete", connect);
                SqlParameter param_CategoryId = command.Parameters.Add("@CategoryId", SqlDbType.Int);

                command.CommandType = CommandType.StoredProcedure;
                param_CategoryId.Value = categoryId;

                command.Connection.Open();
                command.ExecuteNonQuery();
                command.Connection.Close();
            }
        }
        public void GetByAlias()
        {
            using(SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Categories_GetByAlias", connect);
                SqlParameter param_Alias = command.Parameters.Add("@Alias", SqlDbType.NVarChar, 100);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                param_Alias.Value = alias;

                command.Connection.Open();
                reader = command.ExecuteReader();
                if(reader.Read())
                {
                    categoryId = Convert.ToInt32(reader.GetValue(0));
                    name = reader.GetValue(1).ToString().Trim();
                    alias = reader.GetValue(2).ToString().Trim();
                    imageUrl = reader.GetValue(3).ToString().Trim();
                    parentId = Convert.ToInt32(reader.GetValue(4));
                    isVisible = Convert.ToBoolean(reader.GetValue(5));                   
                }
                reader.Close();
                command.Connection.Close();
            }
        }
        public void GetById()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Categories_GetById", connect);
                SqlParameter param_CategoryId = command.Parameters.Add("@CategoryId", SqlDbType.Int);
                SqlDataReader reader = null;

                command.CommandType = CommandType.StoredProcedure;
                param_CategoryId.Value = categoryId;

                command.Connection.Open();
                reader = command.ExecuteReader();
                if (reader.Read())
                {
                    categoryId = Convert.ToInt32(reader.GetValue(0));
                    name = reader.GetValue(1).ToString().Trim();
                    alias = reader.GetValue(2).ToString().Trim();
                    imageUrl = reader.GetValue(3).ToString().Trim();
                    parentId = Convert.ToInt32(reader.GetValue(4));
                    isVisible = Convert.ToBoolean(reader.GetValue(5)); 
                }
                reader.Close();
                command.Connection.Close();
            }
        }

        public List<Category> GetParrents()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Categories_GetRoots", connect);
                SqlDataReader reader = null;

                CategoryList = new List<Category>();
                command.CommandType = CommandType.StoredProcedure;
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cat = new Category();
                    cat.CategoryId = (int)reader["CategoryId"];
                    cat.GetById();
                    CategoryList.Add(cat);
                }
                reader.Close();
                command.Connection.Close();
            }
            return CategoryList;
        }
        public List<Category> GetChilds()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Categories_GetByParentId", connect);
                SqlParameter param_ParentId = command.Parameters.Add("@ParentId", SqlDbType.Int);
                SqlDataReader reader = null;

                CategoryList = new List<Category>();
                command.CommandType = CommandType.StoredProcedure;
                param_ParentId.Value = categoryId;
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cat = new Category();
                    cat.CategoryId = (int)reader["CategoryId"];
                    cat.GetById();
                    CategoryList.Add(cat);
                }
                reader.Close();
                command.Connection.Close();
            }
            return CategoryList;
        }
        public List<Category> GetAll()
        {
            using (SqlConnection connect = new SqlConnection(DBHelper.ConnectionString))
            {
                SqlCommand command = new SqlCommand("Categories_GetAll", connect);
                SqlDataReader reader = null;

                CategoryList = new List<Category>();
                command.CommandType = CommandType.StoredProcedure;
                command.Connection.Open();
                reader = command.ExecuteReader();
                while (reader.Read())
                {
                    Category cat = new Category();
                    cat.CategoryId = (int)reader["CategoryId"];
                    cat.GetById();
                    CategoryList.Add(cat);
                }
                command.Connection.Close();
            }
            return CategoryList;
        }
    }
}
